package servlet;

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

public class projectServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	protected String database = "";
	protected String port = "";
	protected String username = "";
	protected String password = "";

	public projectServlet() {

		parseXML("/Users/emperor1412/Desktop/ServerConfig.xml");
	}

	public ResultSet ExecuteQuery2(String sql) {

		try {
			Class.forName("org.postgresql.Driver");
			String url = "jdbc:postgresql://localhost:"
					+ (port.equalsIgnoreCase("") ? "5432" : port) + "/"
					+ (database.equalsIgnoreCase("") ? "buzzcomm" : database);
			Properties props = new Properties();
			props.setProperty("user",
					username.equalsIgnoreCase("") ? "postgres" : username);
			props.setProperty("password",
					password.equalsIgnoreCase("") ? "123456" : password);
			Connection connection = DriverManager.getConnection(url, props);
			Statement stmt = connection.createStatement();
			ResultSet result = stmt.executeQuery(sql);
			return result;
			// stmt.close();
			// connection.close();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return null;
	}

	public boolean ExecuteQuery(String sql) {
		boolean result = true;
		try {
			Class.forName("org.postgresql.Driver");
			String url = "jdbc:postgresql://localhost:"
					+ (port.equalsIgnoreCase("") ? "5432" : port) + "/"
					+ (database.equalsIgnoreCase("") ? "buzzcomm" : database);
			Properties props = new Properties();
			props.setProperty("user",
					username.equalsIgnoreCase("") ? "postgres" : username);
			props.setProperty("password",
					password.equalsIgnoreCase("") ? "123456" : password);
			Connection connection = DriverManager.getConnection(url, props);
			Statement stmt = connection.createStatement();
			result = stmt.execute(sql);
			stmt.close();
			connection.close();
		} catch (ClassNotFoundException ex) {
			ex.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return result;
	}

	protected void parseXML(String filename) {
		try {
			System.out.println("filename : " + filename);
			File file = new File(filename);
			if (file.exists()) {
				DocumentBuilderFactory factory = DocumentBuilderFactory
						.newInstance();
				DocumentBuilder builder = factory.newDocumentBuilder();
				Document doc = builder.parse(filename);
				NodeList list = doc.getElementsByTagName("*");
				for (int i = 0; i < list.getLength(); i++) {
					Element element = (Element) list.item(i);
					System.out.println(element.getNodeName() + " : "
							+ element.getTextContent());
					if (element.getNodeName().equalsIgnoreCase("database")) {
						database = element.getTextContent();
					} else if (element.getNodeName().equalsIgnoreCase("port")) {
						port = element.getTextContent();
					} else if (element.getNodeName().equalsIgnoreCase(
							"username")) {
						username = element.getTextContent();
					} else if (element.getNodeName().equalsIgnoreCase(
							"password")) {
						password = element.getTextContent();
					}
				}
			} else {
				System.out.print("File not found!");
			}
		} catch (Exception e) {
			System.exit(1);
		}
	}

	protected void insertRecord(String name, String code, String description,
			String id_type, String pm, String is_template, String status,
			String plan_start_date, String plan_end_date,
			String real_start_date, String real_end_date, String creator,
			String deleted, String id_company) {
		String query = "select sp_project_insert(character varying(255) '"
				+ name + "'" + ", character varying(20) '" + code + "'"
				+ ", character varying '" + description + "'" + ", integer '"
				+ id_type + "'" + ", integer '" + pm + "'" + ", boolean '"
				+ is_template + "'" + ", integer '" + status + "'"
				+ ", timestamp without time zone '" + plan_start_date + "'"
				+ ", timestamp without time zone '" + plan_end_date + "'"
				+ ", timestamp without time zone '" + real_start_date + "'"
				+ ", timestamp without time zone '" + real_end_date + "'"
				+ ", integer '" + creator + "'" + ", boolean '" + deleted + "'"
				+ ", integer '" + id_company + "'" + ");";

		System.out.println("query : " + query);
		ExecuteQuery(query);
	}

	protected void processRequest(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html;charset=UTF-8");
		PrintWriter out = response.getWriter();
		HttpSession session = request.getSession(true);
		try {
			if (request.getParameter("insert") != null) {

				String name = request.getParameter("name");
				String code = request.getParameter("code");
				String description = request.getParameter("description");
				String id_type = request.getParameter("id_type");
				String pm = request.getParameter("pm");
				String is_template = request.getParameter("is_template");
				String status = request.getParameter("status");
				String plan_start_date = request.getParameter("plan_start_date");
				String plan_end_date = request.getParameter("plan_end_date");
				String real_start_date = request.getParameter("real_start_date");
				String real_end_date = request.getParameter("real_end_date");
				String creator = request.getParameter("creator");
				String deleted = request.getParameter("deleted");
				String id_company = request.getParameter("id_company");

//				parseXML(getServletContext().getRealPath("META-INF/ServerConfig.xml"));
				insertRecord(name, code, description, id_type, pm, is_template, status, plan_start_date, plan_end_date, real_start_date, real_end_date, creator, deleted, id_company);
			}

			else if (request.getParameter("select") != null) {
				String id = request.getParameter("id");
				String query = "select * from project where id = " + (id == null ? "8" : id);
				ResultSet result = ExecuteQuery2(query);
				if (result.next()) {
					String name = result.getString("name");
					String code = result.getString("code");
					String description = result.getString("description");
					String id_type = result.getString("id_type");
					String pm = result.getString("pm");
					String is_template = result.getString("is_template");
					String status = result.getString("status");
					String plan_start_date = result.getString("plan_start_date");
					String plan_end_date = result.getString("plan_end_date");
					String real_start_date = result.getString("real_start_date");
					String real_end_date = result.getString("real_end_date");
					String created = result.getString("created");
					String creator = result.getString("creator");
					String updated = result.getString("updated");
					String updator = result.getString("updator");
					String deleted = result.getString("deleted");
					String deletion_time = result.getString("deletion_time");
					String id_company = result.getString("id_company");

					session.setAttribute("id", id);
					session.setAttribute("name", name);
					session.setAttribute("code", code);
					session.setAttribute("description", description);
					session.setAttribute("id_type", id_type);
					session.setAttribute("pm", pm);
					session.setAttribute("is_template", is_template);
					session.setAttribute("status", status);
					session.setAttribute("plan_start_date", plan_start_date);
					session.setAttribute("plan_end_date", plan_end_date);
					session.setAttribute("real_start_date", real_start_date);
					session.setAttribute("real_end_date", real_end_date);
					session.setAttribute("created", created);
					session.setAttribute("creator", creator);
					session.setAttribute("updated", updated);
					session.setAttribute("updator", updator);
					session.setAttribute("deleted", deleted);
					session.setAttribute("deletion_time", deletion_time);
					session.setAttribute("id_company", id_company);
					
				}
				response.sendRedirect("test.jsp");

			}

		} catch (Exception ex) {
			System.out.println("there is error");
			ex.printStackTrace();
		} finally {
			out.close();
		}
	}

	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		processRequest(request, response);
	}

}
